package com.pinc.exam;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;

import android.content.ContentValues;
import android.content.Context;
import android.content.res.Resources;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.text.TextUtils;
import android.text.format.Time;

public class DBClass {

	private static final int DATABASE_VERSION = 2;
	private static final String DATABASE_NAME = "DB_EXAM";
	public SQLiteDatabase mDB;
	/* Question Library Table */
	public static final String TABLE_NAME_QLIB = "QLIB";
	public static final String COL_QLIB_QUESTION = "question";
	public static final String COL_QLIB_ANS = "ans";
	public static final String COL_QLIB_SEL1 = "sel1";
	public static final String COL_QLIB_SEL2 = "sel2";
	public static final String COL_QLIB_SEL3 = "sel3";
	public static final String COL_QLIB_SEL4 = "sel4";
	public static final String COL_QLIB_SEL5 = "sel5";
	public static final String COL_QLIB_SEL_TOTAL = "total";
	public static final String COL_QLIB_TESTED_NO = "tested";
	public static final String COL_QLIB_BINGO_NO = "bingo";
	public static final String COL_QLIB_CATEGORY = "category";
	public static final String COL_QLIB_CREDIT = "credit";
	public static final String COL_QLIB_LAST_TEST = "last_test";
	public static final String COL_QLIB_LAST_CREDIT = "last_credit";
	public static final String COL_QLIB_LAST_RESULT = "last_result";
	public static final String COL_QLIB_IMAGE = "image";

	/* Category Table */
	public static final String TABLE_NAME_CATEGORY = "QCTG";
	public static final String COL_QCTG_NAME = "name";

	/* Group Index Table */
	public static final String TABLE_NAME_GROUPS = "GROUPS";
	public static final String COL_GROUPS_NAME = "name";
	public static final String COL_GROUPS_TIMESTAMP_CREATE = "timestamp_create";

	/* Missed List Table */
	public static final String TABLE_NAME_MISSED_LIST = "MISSEDLIST";
	public static final String COL_MISSED_LIST_QID = "q_id";
	/* Group */
	public static final String MY_GROUP_TABLE_PREFIX = "MY_GROUP_";
	public static final String COL_GROUP_QID = "q_id";

	private final DBOpenHelper mHelper;

	public DBClass(Context context) {
		mHelper = new DBOpenHelper(context);
	}

	public void init()
	{
		mHelper.getWritableDatabase().close();
	}

	public Cursor getRecord(String table, String whereclause, String[] args, String orderby, String limit)
	{
		mDB = mHelper.getReadableDatabase();
		Cursor cs = mDB.query(table, null, whereclause, args, null, null, orderby, limit);
		return cs;
	}

	public Cursor SQLQuery(String sql, String[] args)
	{
		mDB = mHelper.getReadableDatabase();
		Cursor cs = mDB.rawQuery(sql, args);
		return cs;
	}

	public Cursor getQuestionsInMissedList()
	{
		mDB = mHelper.getReadableDatabase();
		String SQL_Str = "SELECT Q.* FROM " + TABLE_NAME_QLIB +" Q, " + TABLE_NAME_MISSED_LIST + 
		" P WHERE Q._id = P." + COL_MISSED_LIST_QID + " ORDER BY P._id DESC";
		Cursor cs = mDB.rawQuery(SQL_Str, null);
		return cs;
	}

	public void addQuestion2MissedList(long qid)
	{
		mDB = mHelper.getWritableDatabase();
		mDB.delete(TABLE_NAME_MISSED_LIST, COL_MISSED_LIST_QID + "= ?", new String[]{String.valueOf(qid)});
		ContentValues cv = new ContentValues();
		cv.put(COL_MISSED_LIST_QID, qid);
		mDB.insert(TABLE_NAME_MISSED_LIST, null, cv);
		mDB.close();
	}

	public void delQuestionInMissedList(long qid)
	{
		mDB = mHelper.getWritableDatabase();
		mDB.delete(TABLE_NAME_MISSED_LIST, COL_MISSED_LIST_QID + "= ?", new String[]{String.valueOf(qid)});
		mDB.close();
	}

	public Cursor getQuestionsInGroup(long gid)
	{
		mDB = mHelper.getReadableDatabase();
		String SQL_Str = "SELECT Q.*, P." + COL_GROUP_QID +" FROM " + TABLE_NAME_QLIB +" Q, " + MY_GROUP_TABLE_PREFIX + gid + " P WHERE Q._id = P." + COL_GROUP_QID;
		Cursor cs = mDB.rawQuery(SQL_Str, null);
		return cs;
	}

	public int getQuestionNoInGroup(long gid)
	{
		Cursor cs = getQuestionsInGroup(gid);
		int ret = cs.getCount();
		cs.close();
		mDB.close();
		return ret;
	}

	public int getQuestionNoInGroups(long[] gid)
	{
		boolean first = true;
		String sql = "";
		for ( int i = 0; i< gid.length;i++)
		{
			if (!first)
				sql += " UNION ";
			sql += ("SELECT " + DBClass.COL_GROUP_QID + " FROM " + DBClass.MY_GROUP_TABLE_PREFIX + gid[i]);
			first=false;
		}

		Cursor cs = SQLQuery(sql, null);
		int ret = cs.getCount();
		cs.close();
		mDB.close();
		return ret;
	}
	public int getQuestionNoInMissedList()
	{
		Cursor cs;
		cs = getRecord(TABLE_NAME_MISSED_LIST, null, null, null, null);
		int ret = cs.getCount();
		cs.close();
		mDB.close();
		return ret;
	}

	public int getQuestionNoInCategory(long cid)
	{
		Cursor cs;
		if ( cid < 0)
			cs = getRecord(TABLE_NAME_QLIB, null, null, null, null);
		else
			cs = getRecord(TABLE_NAME_QLIB, COL_QLIB_CATEGORY + " = ?", new String[]{String.valueOf(cid)}, null, null);
		int ret = cs.getCount();
		cs.close();
		mDB.close();
		return ret;
	}

	public int getQuestionNoInCategoryNotInGroup(long cid, long gid)
	{
		Cursor cs = getQuestionsNotInGroup(gid, cid);
		int ret = cs.getCount();
		cs.close();
		mDB.close();
		return ret;
	}

	public void creditQuestion(int id, float credit, Time new_time)
	{
		mDB = mHelper.getWritableDatabase();
		String SQL_str = "UPDATE " + TABLE_NAME_QLIB + " SET " + COL_QLIB_CREDIT + " = " + credit + " , " + COL_QLIB_LAST_CREDIT + " = " + new_time.format2445();
		SQL_str += " WHERE _id = " + id;
		mDB.execSQL(SQL_str);
		mDB.close();
	}

	public void updateStatQuestion(int id, boolean isCorrect)
	{
		ContentValues cv = new ContentValues();
		Time now = new Time();
		mDB = mHelper.getWritableDatabase();
		String SQL_str = "UPDATE " + TABLE_NAME_QLIB + " SET " + COL_QLIB_TESTED_NO + " = (" + COL_QLIB_TESTED_NO + " + 1)";
		now.setToNow();
		/* update tested record */
		if ( isCorrect)
		{
			SQL_str += (", " + COL_QLIB_LAST_RESULT + " = 1, " + COL_QLIB_CREDIT + " = (" + COL_QLIB_CREDIT + " * 0.7 + 3), " + COL_QLIB_BINGO_NO + " = (" + COL_QLIB_BINGO_NO + " + 1)"); 
		}
		else
		{
			SQL_str += (", " + COL_QLIB_LAST_RESULT + " = 0, " + COL_QLIB_CREDIT + " = (" + COL_QLIB_CREDIT + " * 0.5)");
		}

		SQL_str += String.format(" WHERE _id = %d", id);
		mDB.execSQL(SQL_str);

		/* set time */
		cv.put(COL_QLIB_LAST_TEST, now.format2445());
		cv.put(COL_QLIB_LAST_CREDIT, now.format2445());
		mDB.update(TABLE_NAME_QLIB, cv, "_id = ?", new String[]{String.format("%d", id)});

		mDB.close();
	}

	/*********** Group Operation Method *************/
	//	public void updateStatPaper(int id, float grade)
	//	{
	//		ContentValues cv = new ContentValues();
	//		Time now = new Time();
	//		mDB = mHelper.getWritableDatabase();
	//
	//		String SQL_str = "UPDATE " + TABLE_NAME_GROUPS + " SET " + COL_GROUPS_TESTED_NO + " = (" + COL_GROUPS_TESTED_NO + " + 1)";
	//		now.setToNow();
	//
	//		SQL_str += (", " + COL_GROUPS_LAST_GRADE + " = " + grade + ", " + COL_GROUPS_AVG_SCORE + " = ( (" + COL_GROUPS_AVG_SCORE + " * " + COL_GROUPS_TESTED_NO + " + " + grade + " ) " + "/" + " (" + COL_GROUPS_TESTED_NO + " + 1) )");
	//
	//		SQL_str += String.format(" WHERE _id = %d", id);
	//		mDB.execSQL(SQL_str);
	//
	//		/* set time */
	//		cv.put(COL_GROUPS_LAST_TEST, now.format2445());
	//		mDB.update(TABLE_NAME_GROUPS, cv, "_id = ?", new String[]{String.format("%d", id)});
	//
	//		mDB.close();
	//	}

	public long addGroup(String name)
	{
		ContentValues cv = new ContentValues();
		cv.put(COL_GROUPS_NAME, name);
		mDB = mHelper.getWritableDatabase();
		long id = mDB.insert(TABLE_NAME_GROUPS, null, cv);
		if (id < 0)
		{
			mDB.close();
			return -1;
		}
		mDB.execSQL("DROP TABLE IF EXISTS " + MY_GROUP_TABLE_PREFIX + id);
		mDB.execSQL("CREATE TABLE " + MY_GROUP_TABLE_PREFIX + id + DBOpenHelper.MY_GROUP_TABLE_CREATE_POSTFIX);
		mDB.close();
		return id;
	}

	public void deleteGroup(long gid)
	{
		SQLiteDatabase db = mHelper.getWritableDatabase();
		db.execSQL("DROP TABLE IF EXISTS " + MY_GROUP_TABLE_PREFIX + String.valueOf(gid));
		db.delete(TABLE_NAME_GROUPS, "_id = ?", new String[]{String.valueOf(gid)});
		db.close();
	}

	public void clearGroup(long gid)
	{
		SQLiteDatabase db = mHelper.getWritableDatabase();
		db.delete(MY_GROUP_TABLE_PREFIX + String.valueOf(gid), null, null);
		db.close();
	}

	public void delQuestionInGroup(long qid, long gid)
	{
		SQLiteDatabase db = mHelper.getWritableDatabase();
		db.delete(MY_GROUP_TABLE_PREFIX + gid, COL_GROUP_QID + " = ?", new String[]{String.valueOf(qid)});
		db.close();
	}

	public Cursor getQuestionsNotInGroup(long gid, long cid)
	{
		mDB = mHelper.getReadableDatabase();
		String Str_AllCtg = (cid < 0)? "": " AND Q." + COL_QLIB_CATEGORY + " = ?";
		String SQL_Str = "SELECT Q.* FROM " + TABLE_NAME_QLIB +" Q LEFT JOIN " + MY_GROUP_TABLE_PREFIX + gid + 
		" P ON Q._id = P." + COL_GROUP_QID +" WHERE P." + COL_GROUP_QID + " IS NULL" + Str_AllCtg;
		Cursor cs = mDB.rawQuery(SQL_Str, (cid < 0)? null:new String[]{String.valueOf(cid)});
		return cs;
	}

	public void addQuestion2Group(long gid, long qid)
	{
		mDB = mHelper.getWritableDatabase();
		ContentValues cv = new ContentValues();
		cv.put(COL_GROUP_QID, qid);
		mDB.insert(MY_GROUP_TABLE_PREFIX+String.valueOf(gid), null, cv);
		mDB.close();
	}

	public void renameGroup(long gid, String name)
	{
		mDB = mHelper.getWritableDatabase();
		ContentValues cv = new ContentValues();
		cv.put(COL_GROUPS_NAME, name);
		mDB.update(TABLE_NAME_GROUPS, cv, "_id = ?", new String[]{String.valueOf(gid)});
		mDB.close();
	}

	public String getGroupName(long gid)
	{
		mDB = mHelper.getReadableDatabase();
		Cursor cs = mDB.query(TABLE_NAME_GROUPS, new String[]{COL_GROUPS_NAME}, "_id = ?", new String[]{String.valueOf(gid)}, null, null, null);
		cs.moveToFirst();
		String name = cs.getString(cs.getColumnIndex(COL_GROUPS_NAME));
		cs.close();
		mDB.close();
		return name;
	}

	public void restoreDefault()
	{
		mDB = mHelper.getWritableDatabase();
		mDB.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME_QLIB);
		mDB.execSQL(DBOpenHelper.TABLE_CREATE_QLIB);
		mDB.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME_CATEGORY);
		mDB.execSQL(DBOpenHelper.TABLE_CREATE_QCTG);
		mDB.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME_GROUPS);
		mDB.execSQL(DBOpenHelper.TABLE_CREATE_GROUPS);
		mDB.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME_MISSED_LIST);
		mDB.execSQL(DBOpenHelper.TABLE_CREATE_MISSED_LIST);
		mHelper.loadDefaultDatabase();
		mDB.close();
	}

	/******************************** Helper Class ***********************************/
	private static class DBOpenHelper extends SQLiteOpenHelper {
		private final Context mContext;
		private SQLiteDatabase mDatabase;

		private static final String TABLE_CREATE_QLIB =
			"CREATE TABLE " + TABLE_NAME_QLIB + " (" +
			"_id INTEGER PRIMARY KEY, " +
			COL_QLIB_QUESTION + " TEXT, " +
			COL_QLIB_IMAGE + " INTEGER DEFAULT -1, " +
			COL_QLIB_SEL1 + " TEXT, " +
			COL_QLIB_SEL2 + " TEXT, " +
			COL_QLIB_SEL3 + " TEXT, " +
			COL_QLIB_SEL4 + " TEXT, " +
			COL_QLIB_SEL5 + " TEXT, " +
			COL_QLIB_ANS + " TEXT, " +
			COL_QLIB_SEL_TOTAL + " INTEGER, " +
			COL_QLIB_TESTED_NO + " INTEGER DEFAULT 0, " +
			COL_QLIB_BINGO_NO + " INTEGER DEFAULT 0, " +
			COL_QLIB_CREDIT + " REAL DEFAULT 1, " +
			COL_QLIB_LAST_CREDIT + " TEXT DEFAULT '', " +
			COL_QLIB_LAST_RESULT + " INTEGER DEFAULT -1, " +
			COL_QLIB_LAST_TEST + " TEXT DEFAULT '', " +
			COL_QLIB_CATEGORY + " INTEGER DEFAULT 0); ";

		private static final String TABLE_CREATE_QCTG =
			"CREATE TABLE " + TABLE_NAME_CATEGORY + " (" +
			"_id INTEGER PRIMARY KEY, " +
			COL_QCTG_NAME + " TEXT); ";

		private static final String TABLE_CREATE_GROUPS =
			"CREATE TABLE " + TABLE_NAME_GROUPS + " (" +
			"_id INTEGER PRIMARY KEY, " +
			COL_GROUPS_NAME + " TEXT DEFAULT '', " +
			COL_GROUPS_TIMESTAMP_CREATE + " TEXT DEFAULT ''); ";

		private static final String TABLE_CREATE_MISSED_LIST =
			"CREATE TABLE " + TABLE_NAME_MISSED_LIST + " (" +
			"_id INTEGER PRIMARY KEY, " +
			COL_MISSED_LIST_QID + " INTEGER ); ";

		private static final String MY_GROUP_TABLE_CREATE_POSTFIX =
			" ( _id INTEGER PRIMARY KEY, " +
			COL_GROUP_QID + " INTEGER ); ";

		public DBOpenHelper(Context context)
		{
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
			mContext = context;
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			mDatabase = db;
			db.execSQL(TABLE_CREATE_QLIB);
			db.execSQL(TABLE_CREATE_QCTG);
			db.execSQL(TABLE_CREATE_GROUPS);
			db.execSQL(TABLE_CREATE_MISSED_LIST);
			loadDefaultDatabase();
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			//db.execSQL("ALTER TABLE " + TABLE_NAME_QLIB + " ADD COLUMN " + COL_SEL3_NAME_QLIB +" TEXT;");
			mDatabase = db;
		}

		@Override
		public void onOpen(SQLiteDatabase db) {
			mDatabase = db;
		}

		public void loadDefaultDatabase() {
			try {
				loadQuestions();
				loadCategories();
			} catch (IOException e) {
				throw new RuntimeException(e);
			}
		}

		private void loadQuestions() throws IOException {
			Resources resources = mContext.getResources();
			InputStream is = resources.openRawResource(R.raw.def_questions);
			BufferedReader reader = new BufferedReader(new InputStreamReader(is));
			try {
				String line;
				while ((line = reader.readLine()) != null) {
					String[] strings = TextUtils.split(line, "@@@");
					if (strings.length < 10) continue;
					long id = addQuestion(strings);
					if (id < 0) {
					}
				}
			} finally {
				reader.close();
				is.close();
			}
			reader.close();
			is.close();
		}

		private void loadCategories() throws IOException {
			Resources resources = mContext.getResources();
			InputStream is = resources.openRawResource(R.raw.qstn_category);
			BufferedReader reader = new BufferedReader(new InputStreamReader(is));
			try {
				String line;
				while ((line = reader.readLine()) != null) {
					String[] strings = TextUtils.split(line, "@@@");
					if (strings.length < 2) continue;
					long id = addCategory(strings);
					if (id < 0) {
					}
				}
			} finally {
				reader.close();
				is.close();
			}
			reader.close();
			is.close();
		}

		public long addQuestion(String[] strarr) {
			ContentValues cv = new ContentValues();
			cv.put(COL_QLIB_QUESTION, strarr[0].trim());
			cv.put(COL_QLIB_IMAGE, Integer.parseInt(strarr[1].trim()));
			cv.put(COL_QLIB_SEL1, strarr[2].trim());
			cv.put(COL_QLIB_SEL2, strarr[3].trim());
			cv.put(COL_QLIB_SEL3, strarr[4].trim());
			cv.put(COL_QLIB_SEL4, strarr[5].trim());
			cv.put(COL_QLIB_SEL5, strarr[6].trim());
			cv.put(COL_QLIB_ANS, strarr[7].trim());
			cv.put(COL_QLIB_SEL_TOTAL, Integer.parseInt(strarr[8].trim()));
			cv.put(COL_QLIB_CATEGORY, Integer.parseInt(strarr[9].trim()));
			return mDatabase.insert(TABLE_NAME_QLIB, null, cv);
		}

		public long addCategory(String[] strarr) {
			ContentValues cv = new ContentValues();
			cv.put("_id", Integer.parseInt(strarr[0].trim()));
			cv.put(COL_QCTG_NAME, strarr[1].trim());
			return mDatabase.insert(TABLE_NAME_CATEGORY, null, cv);
		}
	}

}
